import requests
import psycopg2
from psycopg2.extras import execute_values
import pandas as pd

def convert_online_trace_pg(url):
    pg_conn = getConnect()
    cur = pg_conn.cursor()

    insert_query = "INSERT INTO trace (x, y, time, geom) VALUES %s"
    result = requests.get(url).json()
    values = []
    print("清空表")
    cur.execute("truncate table trace;")
    for trace in result:
        point_x = trace.get("pointX")
        point_y = trace.get("pointY")
        # point_str = "st_geogfromtext'POINT({} {})'.format(point_x, point_y))"
        point_str = 'POINT({} {})'.format(point_x, point_y)
        point_time = trace.get("stateTime")
        value = [point_x, point_y, point_time, point_str]
        values.append(value)
    execute_values(cur, insert_query, values)

    print("提交事务")
    pg_conn.commit()
    pg_conn.close()
    print("执行完成")

def convert_tdengine_trace_pg(file_path):
    insert_query = "INSERT INTO trace (x, y, time, geom) VALUES %s"
    pg_conn = getConnect()
    cur = pg_conn.cursor()

    dataframe = pd.read_csv(file_path)
    points_x = dataframe['coordinate_x']
    points_y = dataframe['coordinate_y']
    times = dataframe['create_time']
    values = []
    print("清空表")
    cur.execute("truncate table trace;")
    for i in range(len(points_x)):
         point_x = points_x[i]
         point_y = points_y[i]
         time = times[i]
         point_str = 'POINT({} {})'.format(point_x, point_y)
         value = [point_x, point_y, time, point_str]
         values.append(value)
    execute_values(cur, insert_query, values)
    print("提交事务")
    pg_conn.commit()
    pg_conn.close()
    print("执行完成")

def getConnect():
    """
    建立数据库连接(库名称，用户名，密码，主机ip，端口)根据实际进行修改
    :return:
    """
    pg_con = psycopg2.connect(database="test", user="postgres", password="egova", host="localhost", port="5432")
    return pg_con




if __name__ == '__main__':
    # convert_online_trace_pg(r"http://110.186.64.120:8081/eUrbanGIS/home/gis/patrol/getobjecttrace.htm?stayAggre=false&filterTrace=false&rectificationType=0&onlyShowgrid=0&objectID=101230&beginDate=2023-11-10%2000%3A00%3A00&endDate=2023-11-10%2023%3A59%3A59&requestType=1&rawData=true&getPatrolPunchClock=false")
    convert_tdengine_trace_pg(r"../resource/trace.csv")
